package org.lex.dbq.model.jdbc.object;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Collections;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.TreeMap;
import java.util.TreeSet;

import org.lex.jdbc.meta.SchemaBean;
import org.lex.jdbc.util.ColumnsArrayListHandler;
import org.lex.jdbc.util.MetaDataHelper;
import org.lex.string.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

public class ObjectTreeBuilder {
	private static final int MAX = 20;
	private static final String[] NULL = { null };
	private static final String[] EMPTY = {};

	private Logger log = LoggerFactory.getLogger(ObjectTreeBuilder.class);

	private Connection conn = null;
	private DatabaseMetaData md = null;
	private MetaDataHelper helper = new MetaDataHelper();
	// db meta data properties
	private String[] tableTypeNames = null;
	private boolean supportCatalog = true;

	public ObjectTreeBuilder(Connection conn) {
		this.conn = conn;
	}

	public ObjectTreeBuilder(DatabaseMetaData md) {
		this.md = md;
	}

	public ObjectNode build() throws SQLException {
		return build(new ObjectNode());
	}

	public ObjectNode build(ObjectNode root) {
		root.setData("root");
		root.setType(ObjectType.FAKE_FOLDER);
		if (null == md) {
			try {
				md = conn.getMetaData();
			} catch (SQLException e) {
				log.error("get DatabaseMetaData failed", e);
				return root;
			}
		}
		tableTypeNames = this.getTableTypes();

		this.buildCatalogNode(root);

		return root;
	}

	private void buildCatalogNode(ObjectNode parent) {
		String[] catalogs = this.getCatalogs();

		for (String catalog : catalogs) {
			// build catalogs
			log.debug("build catalog=[" + catalog + "]");
			ObjectNode catalogNode = null;
			if (StringUtils.isNullOrEmpty(catalog)) {
				log.info("current catalog is null or empty --> use parent node");
				catalogNode = parent;
			} else {
				catalogNode = new ObjectNode();
				catalogNode.setData(catalog);
				catalogNode.setType(ObjectType.CATALOG);
				parent.addChild(catalogNode);
			}
			// build schemas
			this.buildSchemaNode(catalogNode, catalog);
		}
	}

	private void buildSchemaNode(ObjectNode parent, String catalog) {
		String[] schemas = this.getSchemas(catalog);
		for (String schema : schemas) {
			ObjectNode schemaNode = null;
			if (StringUtils.isNullOrEmpty(schema)) {
				log.info("catalog=[" + catalog + "]: current schema is null or empty --> use parent node");
				schemaNode = parent;
			} else {
				schemaNode = new ObjectNode();
				schemaNode.setData(schema);
				schemaNode.setType(ObjectType.SCHEMA);
				parent.addChild(schemaNode);
			}

			// build tables
			this.buildTableNode(schemaNode, catalog, schema);
		}
	}

	private void buildTableNode(ObjectNode parent, String catalog, String schema) {
		Map<String, String> tableMap = this.getTableAndTypes(catalog, schema);
		Set<String> types = this.getTableTypeNames(tableTypeNames, tableMap);

		// add type node: <type name, type node>
		Map<String, ObjectNode> typeNodes = new HashMap<String, ObjectNode>(tableMap.size());
		for (String type : types) {
			ObjectNode typeNode = new ObjectNode();
			typeNode.setData(type);
			typeNode.setType(ObjectType.FAKE_FOLDER);
			parent.addChild(typeNode);

			typeNodes.put(type, typeNode);
		}
		// add table node
		for (Map.Entry<String, String> e : tableMap.entrySet()) {
			String tableName = e.getKey();
			String typeName = e.getValue();
			ObjectNode typeNode = typeNodes.get(typeName);

			ObjectNode tableNode = new ObjectNode();
			tableNode.setData(tableName);
			tableNode.setType(ObjectType.TABLE);
			typeNode.addChild(tableNode);
		}
	}

	private String[] getCatalogs() {
		String[] catalogs;
		try {
			catalogs = helper.getCatalogs(md);
			if (0 == catalogs.length) {
				catalogs = NULL;
				supportCatalog = false;
				log.info("DBMS not support CATALOG");
			}
			if (1 == catalogs.length && true == StringUtils.isNullOrEmpty(catalogs[0])) {
				supportCatalog = false;
				log.info("DBMS not support CATALOG");
			}
		} catch (SQLException e) {
			log.warn("get Catalog failed, using NULL", e);
			catalogs = NULL;
		}
		return catalogs;
	}

	private String[] getSchemas(String catalog) {
		String[] schema;
		try {
			List<SchemaBean> beans = null;
			if (this.supportCatalog) {
				beans = helper.getSchemas(md, catalog, null);
			} else {
				beans = helper.getSchemas(md);
			}
			schema = new String[beans.size()];
			int i = 0;
			for (SchemaBean b : beans) {
				schema[i++] = b.getTABLE_SCHEM();
			}
			if (0 == schema.length) {
				schema = NULL;
			}
		} catch (SQLException e) {
			log.warn("get Schema failed, using NULL", e);
			schema = NULL;
		}
		return schema;
	}

	private String[] getTableTypes() {
		String[] tableTypeNames;
		try {
			tableTypeNames = helper.getTableTypes(md);
		} catch (SQLException e) {
			log.warn("get TableType failed, using EMPTY", e);
			tableTypeNames = EMPTY;
		}
		return tableTypeNames;
	}

	// <table name, table type name>
	private Map<String, String> getTableAndTypes(String catalog, String schema) {
		Map<String, String> tableMap = new TreeMap<String, String>();
		ResultSet rs;
		try {
			rs = md.getTables(catalog, schema, null, null);
		} catch (SQLException e) {
			log.error("get Table failed: catalog=[" + catalog + "], schema=[" + schema + "]", e);
			return tableMap;
		}
		ColumnsArrayListHandler handler = new ColumnsArrayListHandler(3, 4);
		// <table name, table type>
		List<Object[]> tableAndTypeResult;
		try {
			tableAndTypeResult = handler.handle(rs);
		} catch (SQLException e) {
			log.error("read Table ResultSet failed: catalog=[" + catalog + "], schema=[" + schema + "]", e);
			return tableMap;
		}
		for (Object[] r : tableAndTypeResult) {
			tableMap.put((String) r[0], (String) r[1]);
		}
		log.info("catalog=[" + catalog + "] schema=[" + schema + "] have " + tableMap.size() + " tables");
		if (tableMap.size() < MAX) {
			log.debug("tables: " + tableMap);
		}
		return tableMap;
	}

	private Set<String> getTableTypeNames(String[] tableTypeNames, Map<String, String> tableAndTypeMap) {
		Set<String> set = new TreeSet<String>();
		set.addAll(tableAndTypeMap.values());
		Collections.addAll(set, tableTypeNames);
		return set;
	}
}
